package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.HotelBill;
import com.pignest.hotel.beans.Living;
import com.pignest.hotel.beans.ProtocolList;
import com.pignest.hotel.beans.ProtocolPrice;
import com.pignest.hotel.beans.ProtocolUnit;
import com.pignest.hotel.beans.TbAccounts;
import com.pignest.hotel.dao.OtherBusiDao;
import com.pignest.hotel.definition.LivingState;

public class OtherBusiHibernateDao extends BaseHibernateDao implements
		OtherBusiDao {

	@Override
	public List<HotelBill> queryBill(final String roomName,final String name,
			final String orderNo,final String type,final Integer isPrint,final Date sOpTime,final Date eOpTime) {
		// TODO Auto-generated method stub
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from HotelBill as b where 1=1");
				if(name!=null&&!name.equals(""))
					hql.append(" and b.name like :Name");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.roomName =:RoomName");
				if(orderNo!=null&&!orderNo.equals(""))
					hql.append(" and b.orderNo like :OrderNo");
				if(type!=null&&!type.equals(""))
					hql.append(" and b.type=:Type");
				if(isPrint!=null&&!isPrint.equals(""))
					hql.append(" and b.isPrint=:IsPrint");
				if (sOpTime != null)
					hql.append(" and b.opTime>=:SOpTime");
	            if(eOpTime!=null)
	            	hql.append(" and b.opTime<=:EOpTime");
	            
				hql.append(" order by b.opTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(name!=null&&!name.equals(""))
					query.setString("Name", '%' + name.replaceAll("%", "%%") + '%');
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", roomName);
				if(orderNo!=null&&!orderNo.equals(""))
					query.setString("OrderNo",'%'+orderNo.replaceAll("%", "%%") + '%');
				if(type!=null&&!type.equals(""))
					query.setString("Type", type);
				if(isPrint!=null&&!isPrint.equals(""))
					query.setInteger("IsPrint", isPrint);
				if (sOpTime != null)
					query.setDate("SOpTime", sOpTime);
				if (eOpTime != null)
					query.setDate("EOpTime", eOpTime);
				
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}

	@Override
	public List<HotelBill> queryBillPage(final String roomName,final String name,
			final String orderNo,final String type,final Integer isPrint,final Date sOpTime,final Date eOpTime,final int start,final int length) {
		// TODO Auto-generated method stub
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from HotelBill as b where 1=1");
				if(name!=null&&!name.equals(""))
					hql.append(" and b.name like :Name");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.roomName =:RoomName");
				if(orderNo!=null&&!orderNo.equals(""))
					hql.append(" and b.orderNo like :OrderNo");
				if(type!=null&&!type.equals(""))
					hql.append(" and b.type=:Type");
				if(isPrint!=null&&!isPrint.equals(""))
					hql.append(" and b.isPrint=:IsPrint");
				if (sOpTime != null)
					hql.append(" and b.opTime>=:SOpTime");
	            if(eOpTime!=null)
	            	hql.append(" and b.opTime<=:EOpTime");
	            
				hql.append(" order by b.opTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(name!=null&&!name.equals(""))
					query.setString("Name", '%' + name.replaceAll("%", "%%") + '%');
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", roomName);
				if(orderNo!=null&&!orderNo.equals(""))
					query.setString("OrderNo",'%'+orderNo.replaceAll("%", "%%") + '%');
				if(type!=null&&!type.equals(""))
					query.setString("Type", type);
				if(isPrint!=null&&!isPrint.equals(""))
					query.setInteger("IsPrint", isPrint);
				if (sOpTime != null)
					query.setDate("SOpTime", sOpTime);
				if (eOpTime != null)
					query.setDate("EOpTime", eOpTime);
				
				List<Living> list = query.list();
				query.setFirstResult(start);
				query.setMaxResults(length);
				return list;
			}
		});
		return list;
	}
	public List<ProtocolUnit> queryProtocolUnit(final String protocolNo,final String company,final String contactName,final Boolean isHangUp){
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from ProtocolUnit as b where 1=1");
				if(protocolNo!=null&&!protocolNo.equals(""))
					hql.append(" and b.protocolNo like :ProtocolNo");
				if(company!=null&&!company.equals(""))
					hql.append(" and b.company =:Company");
				if(contactName!=null&&!contactName.equals(""))
					hql.append(" and b.contactName like :ContactName");
				if(isHangUp!=null)
				  hql.append(" and b.enableHangUp =:IsHangUp");
				hql.append(" order by b.opTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(protocolNo!=null&&!protocolNo.equals(""))
					query.setString("ProtocolNo", '%' + protocolNo.replaceAll("%", "%%") + '%');
				if(company!=null&&!company.equals(""))
					query.setString("Company", company);
				if(contactName!=null&&!contactName.equals(""))
					query.setString("ContactName",'%'+contactName.replaceAll("%", "%%") + '%');
				if(isHangUp!=null)
				   query.setBoolean("IsHangUp", isHangUp);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	@Override
	public HotelBill queryBillByLivingId(Living l,String type){
		String hql = "from HotelBill as l where l.living=? and l.type=?";
		hql =hql+" order by l.opTime desc";
		List<HotelBill> r = getHibernateTemplate().find(hql, new Object[] {l,type});
		if (r == null || r.isEmpty())
			return null;
		return r.get(0);
	}
	@Override
	public List<ProtocolPrice> queryProtocolPrice(final String protocolId){
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from ProtocolPrice as b where 1=1");
			    hql.append(" and b.protocolUnit.id=:UnitId");
				org.hibernate.Query query = session.createQuery(hql.toString());
				query.setString("UnitId", protocolId);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	public ProtocolPrice queryProtocolPrice(String protocolId,String roomTypeId){
		String hql = "from ProtocolPrice as b where  b.protocolUnit.id=? and b.roomType.id=?";

		List<ProtocolPrice> r = getHibernateTemplate().find(hql, new Object[] {protocolId,roomTypeId});
		if (r == null || r.isEmpty())
			return null;
		return r.get(0);
	}
	@Override
	public List<ProtocolList> queryProtocolList(final String protocolId,
			final String livingId) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from ProtocolList as b where 1=1");
				if(protocolId!=null&&!protocolId.equals(""))
					hql.append(" and b.protocolUnit.id =:ProtocolId");
				if(livingId!=null&&!livingId.equals(""))
					hql.append(" and b.living.id =:LivingId");
				hql.append(" order by b.opTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(protocolId!=null&&!protocolId.equals(""))
					query.setString("ProtocolId", protocolId);
				if(livingId!=null&&!livingId.equals(""))
					query.setString("LivingId", livingId);
				
				List<ProtocolList> list = query.list();
				return list;
			}
		});
		return list;
	}
	public List<ProtocolList> queryUnCheckProtocolList(final String protocolId){
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from ProtocolList as b where 1=1");
				if(protocolId!=null&&!protocolId.equals(""))
					hql.append(" and b.protocolUnit.id =:ProtocolId");
				hql.append(" and b.state='").append(ProtocolList.HANGUP_UNCHECK).append("'");
				hql.append(" order by b.opTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(protocolId!=null&&!protocolId.equals(""))
					query.setString("ProtocolId", protocolId);
				
				
				List<ProtocolList> list = query.list();
				return list;
			}
		});
		return list;
	}
	public int countUnCheckHangUpMoney(String protocolId){
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from ProtocolList b where 1=1");
		hql.append(" and b.state='").append(ProtocolList.HANGUP_UNCHECK).append("'");
	
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		return consum;
	}

}
